﻿using System;
using System.Data.OleDb;
using System.Reflection;
using System.IO;
using System.Data;

namespace DbHelp
{
    /// <summary>
    /// Microsoft Access database operations class.
    /// </summary>
    public partial class AccessHelper : HelperBase
    {
        static string mUerDbPath = IniReadValue("SERVER", "MDBPATH", Environment.CurrentDirectory + @"\sysconfig.ini");

        static string _accessFPath = get_defualt_dbpath(mUerDbPath);

        static string mConnecnStr = f_connStr();

        public virtual string AccessFPath
        {
            get
            {
                return _accessFPath;
            }
            set
            {
                _accessFPath = value;
            }
        }

        public AccessHelper()
        {
            this.Connection = new OleDbConnection();
            Command = Connection.CreateCommand();
        }

        public override void Open()
        {
            this.Connection.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;data source=" + AccessFPath;
            base.Open();
        }
        public static string f_connStr()
        {
            string mXx = "Provider=Microsoft.Jet.Oledb.4.0;data source=" + _accessFPath;
            return mXx;
        }

        public static string f_connStr(string pDbPath)
        {
            string mXx = "Provider=Microsoft.Jet.Oledb.4.0;data source=" + pDbPath;
            return mXx;
        }


        public AccessHelper(string accessfpath)
        {
            this.AccessFPath = accessfpath;
            Open();
        }

        public int ExecuteNoneQuery(string accessfpath)
        {
            return base.ExecuteNoneQuery();
        }



        public static DataSet ExecuteQueryToDataSet(string pSql)
        {

            CommandType cmdType = CommandType.Text;
            OleDbParameter[] cmdParms = new OleDbParameter[] { };
            cmdParms = null;

            DataSet mReturtDs = new DataSet();
            OleDbCommand cmd = new OleDbCommand();
            using (OleDbConnection myConnection = new OleDbConnection(mConnecnStr))
            {
                PrepareCommand(cmd, myConnection, null, cmdType, pSql, cmdParms);
                OleDbDataAdapter DataAdapter = new OleDbDataAdapter();
                DataAdapter.SelectCommand = cmd;

                DataAdapter.Fill(mReturtDs);
                myConnection.Close();
            }
            return mReturtDs;
        }

        public static DataSet ExecuteQueryToDataSet(string pSql, string pDbPath)
        {

            CommandType cmdType = CommandType.Text;
            OleDbParameter[] cmdParms = new OleDbParameter[] { };
            cmdParms = null;

            DataSet mReturtDs = new DataSet();
            OleDbCommand cmd = new OleDbCommand();
            using (OleDbConnection myConnection = new OleDbConnection(f_connStr(pDbPath)))
            {
                PrepareCommand(cmd, myConnection, null, cmdType, pSql, cmdParms);
                OleDbDataAdapter DataAdapter = new OleDbDataAdapter();
                DataAdapter.SelectCommand = cmd;

                DataAdapter.Fill(mReturtDs);
                myConnection.Close();
                DataAdapter.Dispose();
            }
            return mReturtDs;
        }


        /// <summary>
        /// 使用DataSet中的数据进行对数据源的相应表的数据进行更新
        /// </summary>
        /// <param name="connString">数据库联接字符串</param>
        /// <param name="selectCmd">其中结果集的相应的选择语句</param>
        /// <param name="srcTable">相应的数据源中的源表的表名</param>
        /// <param name="updateDataSet">用于更新数据的数据集DataSet</param>
        /// <returns></returns>
        public static bool UpdateByDataSet(string selectCmd, DataSet updateDataSet)
        {

            bool sucess = false;
            if (updateDataSet.HasChanges())
            {
                using (OleDbConnection myConnection = new OleDbConnection(mConnecnStr))
                {
                    myConnection.Open();
                    OleDbDataAdapter da = new OleDbDataAdapter(selectCmd, myConnection);
                    OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                    try
                    {
                        da.Update(updateDataSet, updateDataSet.Tables[0].TableName);
                        updateDataSet.AcceptChanges();
                        sucess = true;
                    }
                    catch (Exception ex)
                    {
                        updateDataSet.RejectChanges();
                        throw ex;
                    }
                    finally
                    {
                        myConnection.Close();
                    }
                }
                return sucess;
            }
            else
                return sucess;
        }

        /// <summary>
        /// 使用DataSet中的数据进行对数据源的相应表的数据进行更新
        /// </summary>
        /// <param name="connString">数据库联接字符串</param>
        /// <param name="selectCmd">其中结果集的相应的选择语句</param>
        /// <param name="srcTable">相应的数据源中的源表的表名</param>
        /// <param name="updateDataSet">用于更新数据的数据集DataSet</param>
        /// <returns></returns>
        public static bool UpdateByDataSet(string selectCmd, DataSet updateDataSet, string pDbPath)
        {

            bool sucess = false;
            if (updateDataSet.HasChanges())
            {
                using (OleDbConnection myConnection = new OleDbConnection(f_connStr(pDbPath)))
                {
                    myConnection.Open();
                    OleDbDataAdapter da = new OleDbDataAdapter(selectCmd, myConnection);
                    OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                    try
                    {
                        da.Update(updateDataSet, updateDataSet.Tables[0].TableName);
                        updateDataSet.AcceptChanges();
                        sucess = true;
                    }
                    catch (Exception ex)
                    {
                        updateDataSet.RejectChanges();
                        throw ex;
                    }
                    finally
                    {
                        myConnection.Close();
                    }
                }
                return sucess;
            }
            else
                return sucess;
        }


        /// <summary>
        /// 使用DataSet中的数据进行对数据源的相应表的数据进行更新
        /// </summary>
        /// <param name="connString">数据库联接字符串</param>
        /// <param name="selectCmd">其中结果集的相应的选择语句</param>
        /// <param name="srcTable">相应的数据源中的源表的表名</param>
        /// <param name="updateDataSet">用于更新数据的数据集DataSet</param>
        /// <returns></returns>
        public static int UpdateByDataSet(string selectCmd, DataSet updateDataSet, string pDbPath, bool pReturnMaxIID)
        {

            int mReturnMaxIID = 0;
            if (updateDataSet.HasChanges())
            {
                using (OleDbConnection myConnection = new OleDbConnection(f_connStr(pDbPath)))
                {
                    myConnection.Open();
                    OleDbDataAdapter da = new OleDbDataAdapter(selectCmd, myConnection);
                    OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                    try
                    {
                        da.Update(updateDataSet, updateDataSet.Tables[0].TableName);
                        updateDataSet.AcceptChanges();
                        //执行取最大值sql
                        OleDbCommand command = new OleDbCommand(@"select @@identity");

                        // Set the Connection to the new OleDbConnection.
                        command.Connection = myConnection;

                        // Open the connection and execute the insert command.
                        try
                        {
                            //connection.Open();
                            mReturnMaxIID = (int)command.ExecuteScalar();
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }


                    }
                    catch (Exception ex)
                    {
                        updateDataSet.RejectChanges();
                        throw ex;
                    }
                    finally
                    {
                        myConnection.Close();
                    }
                }
                return mReturnMaxIID;
            }
            else
                return mReturnMaxIID;
        }



        /// <summary>
        /// 使用DataSet中的数据进行对数据源的相应表的数据进行更新
        /// </summary>
        /// <param name="connString">数据库联接字符串</param>
        /// <param name="selectCmd">其中结果集的相应的选择语句</param>
        /// <param name="srcTable">相应的数据源中的源表的表名</param>
        /// <param name="updateDataSet">用于更新数据的数据集DataSet</param>
        /// <returns></returns>
        public static bool UpdateByDataSet(string connString, string selectCmd, string srcTable, DataSet updateDataSet)
        {
            bool sucess = false;
            if (updateDataSet.HasChanges())
            {
                using (OleDbConnection myConnection = new OleDbConnection(connString))
                {
                    myConnection.Open();
                    OleDbDataAdapter da = new OleDbDataAdapter(selectCmd, myConnection);
                    OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                    try
                    {
                        da.Update(updateDataSet, srcTable);
                        updateDataSet.AcceptChanges();
                        sucess = true;
                    }
                    catch (Exception ex)
                    {
                        updateDataSet.RejectChanges();
                        throw ex;
                    }
                    finally
                    {
                        myConnection.Close();
                    }
                }
                return sucess;
            }
            else
                return sucess;
        }


        public static DataSet ExecuteQueryToDataSet(string pSql, CommandType cmdType, OleDbParameter[] cmdParms)
        {

            cmdType = CommandType.Text;

            DataSet mReturtDs = new DataSet();
            OleDbCommand cmd = new OleDbCommand();
            using (OleDbConnection myConnection = new OleDbConnection(_accessFPath))
            {
                PrepareCommand(cmd, myConnection, null, cmdType, pSql, cmdParms);
                OleDbDataAdapter DataAdapter = new OleDbDataAdapter();
                DataAdapter.SelectCommand = cmd;

                DataAdapter.Fill(mReturtDs);
                myConnection.Close();
            }
            return mReturtDs;
        }

        /// <summary>
        /// 提供一个SqlCommand对象的设置
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="conn">SqlConnection 对象</param>
        /// <param name="trans">SqlTransaction 对象</param>
        /// <param name="cmdType">CommandType 如存贮过程，T-SQL</param>
        /// <param name="cmdText">存贮过程名或查询串</param>
        /// <param name="cmdParms">命令中用到的参数集</param>
        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (OleDbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


        public OleDbParameter AddParameter(string ParameterName, OleDbType type, object value)
        {
            return AddParameter(ParameterName, type, value, ParameterDirection.Input);
        }

        public OleDbParameter AddParameter(string ParameterName, OleDbType type, object value, ParameterDirection direction)
        {
            OleDbParameter param = new OleDbParameter(ParameterName, type);
            param.Value = value;
            param.Direction = direction;
            Command.Parameters.Add(param);
            return param;
        }

        public OleDbParameter AddParameter(string ParameterName, OleDbType type, int size, object value)
        {
            return AddParameter(ParameterName, type, size, value, ParameterDirection.Input);
        }

        public OleDbParameter AddParameter(string ParameterName, OleDbType type, int size, object value, ParameterDirection direction)
        {
            OleDbParameter param = new OleDbParameter(ParameterName, type, size);
            param.Direction = direction;
            param.Value = value;
            Command.Parameters.Add(param);
            return param;
        }

        public void AddRangeParameters(OleDbParameter[] parameters)
        {
            Command.Parameters.AddRange(parameters);
        }


    }
}
